package com.apes.fn.crm.ReturnVisit.repository;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.apes.framework.query.ApesDao;
import org.springframework.stereotype.Component;

@Component
public class ReturnVisitDao extends ApesDao {


    /***
     * 今日新增
     */
    public JSONArray getAdd(JSONObject param) {
        String sSQL = "SELECT A.VISITOR_ID,  COUNT(A.ID) ADD_NUM\n" +
                "  FROM FN_RETURN_VISIT A\n" +
                " WHERE TRUNC(A.SHOULD_VISIT_DATE) = TRUNC(:EXECUTE_DATE)\n" +
                " GROUP BY A.VISITOR_ID";

        return toJSONArray(find(sSQL, param));
    }

    /***
     * 待回访
     */
    public JSONArray getWaitVisit(JSONObject param) {
        String sSQL = "SELECT A.VISITOR_ID, COUNT(A.ID) WAIT_VISIT\n" +
                "  FROM FN_RETURN_VISIT A\n" +
                " WHERE (A.STATE = 'create' AND TRUNC(A.SHOULD_VISIT_DATE) <= TRUNC(:EXECUTE_DATE)) OR \n" +
                " (A.STATE = 'again' AND TRUNC(A.NEXT_VISIT_DATE) <= TRUNC(:EXECUTE_DATE)) \n" +
                " GROUP BY A.VISITOR_ID";

        return toJSONArray(find(sSQL, param));
    }

    /***
     * 已回访
     */
    public JSONArray getVisited(JSONObject param) {
        String sSQL = "SELECT C.VISITOR_ID, SUM(C.VISITED) VISITED\n" +
                "  FROM (SELECT A.VISITOR_ID, COUNT(A.ID) VISITED\n" +
                "           FROM FN_RETURN_VISIT A\n" +
                "          WHERE A.STATE IN ('end', 'finish')\n" +
                "            AND TRUNC(A.VISITED_DATE) = TRUNC(:EXECUTE_DATE)\n" +
                "          GROUP BY A.VISITOR_ID\n" +
                "         UNION\n" +
                "         SELECT A.VISITOR_ID, COUNT(A.ID) VISITED\n" +
                "           FROM FN_RETURN_VISIT A\n" +
                "          WHERE A.STATE = 'again'\n" +
                "            AND TRUNC(A.NEXT_VISIT_DATE) > TRUNC(:EXECUTE_DATE)\n" +
                "            AND EXISTS\n" +
                "          (SELECT 1\n" +
                "                   FROM FN_VISIT_HISTORY B\n" +
                "                  WHERE A.ID = B.RETURN_VISIT_ID\n" +
                "                    AND TRUNC(B.VISITED_DATE) = TRUNC(:EXECUTE_DATE))\n" +
                "          GROUP BY A.VISITOR_ID) C\n" +
                " GROUP BY C.VISITOR_ID";
        return toJSONArray(find(sSQL, param));
    }

    /***
     * 回访完成
     */
    public JSONArray getFinish(JSONObject param) {
        String sSQL = "SELECT A.VISITOR_ID, COUNT(A.ID) FINISH_NUM\n" +
                "  FROM FN_RETURN_VISIT A\n" +
                " WHERE TRUNC(A.VISITED_DATE) = TRUNC(:EXECUTE_DATE)\n" +
                "   AND A.STATE = 'finish'\n" +
                " GROUP BY A.VISITOR_ID";

        return toJSONArray(find(sSQL, param));
    }

    /***
     * 终止
     */
    public JSONArray getEnd(JSONObject param) {
        String sSQL = "SELECT A.VISITOR_ID, COUNT(A.ID) END_NUM\n" +
                "  FROM FN_RETURN_VISIT A\n" +
                " WHERE TRUNC(A.VISITED_DATE) = TRUNC(:EXECUTE_DATE)\n" +
                "   AND A.STATE = 'finish'\n" +
                " GROUP BY A.VISITOR_ID";

        return toJSONArray(find(sSQL, param));
    }

    /***
     * 继续回访
     */
    public JSONArray getAgain(JSONObject param) {
        String sSQL = "";

        return toJSONArray(find(sSQL, param));
    }

    /***
     * 拨号量
     */
    public JSONArray getCall(JSONObject param) {
        String sSQL = "SELECT A.VISITOR_ID, COUNT(A.ID) CALL_NUM\n" +
                "  FROM FN_VISIT_HISTORY A\n" +
                " WHERE A.VISITED_DATE = TRUNC(:EXECUTE_DATE)\n" +
                " GROUP BY A.VISITOR_ID";

        return toJSONArray(find(sSQL, param));
    }

}
